package base.db.lib.back;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.math.BigDecimal;
import javax.naming.InitialContext;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import javax.sql.DataSource;

import base.db.lib.back.*;

@SuppressWarnings("all")
public class CopyOfDBDAO20101229   {
	public static int JDBC_DRIVER_PROVIDER_UNKNOW = 0;
	public static int JDBC_DRIVER_PROVIDER_ORACLE = 1;
	public static int JDBC_DRIVER_PROVIDER_SYBASE = 2;
	public static int JDBC_DRIVER_PROVIDER_INFORMIX = 3;
	public static int JDBC_DRIVER_PROVIDER_POSTGRES = 4;
	public static int JDBC_DRIVER_PROVIDER_MYSQL = 5;
	public static int JDBC_DRIVER_PROVIDER_SQLSERVER = 6;
	public DatabaseMetaData dbmetadata;
	private Connection connection = null;
	
	/**
	 **************************************************************************
	 * 
	 * Connection  常用属性和常用操作  begin
	 * 
	 **************************************************************************
	 */
	
	/**
	 * 获取驱动类名称
	 * @return
	 * @throws Exception
	 */
	public String getDriverName() throws Exception {
		try {
			return this.dbmetadata.getDriverName();
		} catch (Exception ex) {
			throw ex;
		}
	}
	/**
	 * 设置某种数据类型  获取那种数据库类型
	 * @return  int
	 */
	public int getDriverProvider() throws Exception {
       String drvname = getDriverName();
		if ((drvname == null) || (drvname.equals(""))) {
			return JDBC_DRIVER_PROVIDER_UNKNOW;
		}
		if (drvname.indexOf("Oracle") >= 0) {
			return JDBC_DRIVER_PROVIDER_ORACLE;
		}
		if (drvname.indexOf("Post") >= 0) {
			return JDBC_DRIVER_PROVIDER_POSTGRES;
		}
		if (drvname.indexOf("sybase") >= 0) {
			return JDBC_DRIVER_PROVIDER_SYBASE;
		}
		if (drvname.indexOf("MySQL") >= 0) {
			return JDBC_DRIVER_PROVIDER_MYSQL;
		}
		return JDBC_DRIVER_PROVIDER_UNKNOW;
	}

	public void println(String str) {
		System.out.println(str);
	}
	
	public void setConnection(Connection connection) {
		this.connection = connection;
		log("["+super.getClass().getName()+"]:paramsid:"+connection.toString());
		log("["+super.getClass().getName()+"]:superid:"+this.connection.toString());
		try {
			dbmetadata=	connection.getMetaData();
			this.connection.setAutoCommit(false);
		} catch (Exception e) {
			
			e.printStackTrace();
		}
	}
	public Connection getConnection() {
		return this.connection;
	}
	private Connection conn(String server, String databaseName,
			String Ldbuser, String Ldbpass,String driverName) {
		Connection conn = null;
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(server, Ldbuser,Ldbpass);
			return conn;
		} catch (Exception e) {
			System.out.println("驱动异常");
		}
		return null;
	}
	private Connection conn(String JNDIDatabaseName) {
		try {
			InitialContext ic = new InitialContext();
			DataSource ds = (DataSource) ic.lookup(JNDIDatabaseName);
			return ds.getConnection();
		} catch (Exception sex) {
			sex.printStackTrace();
		}
		return null;
	}
	public void startConnectionJNDID(String JNDIDatabaseName) {
		this.connection = conn(JNDIDatabaseName);
	}
	
	/**
	 * PreparedStatement 对象设置参数值
	 * @param pst PreparedStatement对象
	 * @param vlist  Vector 对象 存放的参数列表值
	 * @throws Exception
	 */
	public void setSQLValues(PreparedStatement pst, Vector vlist)
			throws Exception {
		try {
			if (vlist == null)
				return;
			Object v = null;
			for (int i = 0; i < vlist.size(); ++i) {
				v = vlist.get(i);
				if (v instanceof java.util.Date) {
					java.util.Date d = (java.util.Date) v;
					v = new Timestamp(d.getTime());
				}
				pst.setObject(i + 1, v);
			}

		} catch (Exception ex) {
			log("Error to set the sql values !");
			throw  ex;
		}
	}

	
	/**
	 * 处理分页 语句  根据 不同的数据库 设置分页SQL语句
	 * @param p_SequenceName
	 * @param p_next_or_curr
	 * @return
	 * @throws DBException
	 */
	private String setRownumControl(String p_sql, int p_iStartRow, int p_iEndRow)
			throws Exception {
		int provider = getDriverProvider();
		switch (provider) {
		case 0:
			return "";
		case 1:
			String temp = p_sql;
			return " select * from  " + " ( "
					+ " select t.*,ROWNUM as ORACLE_ROWNUM from " + " ( "
					+ p_sql + " ) " + " t " + " ) " + "WHERE ORACLE_ROWNUM >"
					+ p_iStartRow + " AND ORACLE_ROWNUM <=" + p_iEndRow;
		case 2:
			return null;
		case 3:
			return null;
		case 4:
		    return null;
		case 5:
			String sql=p_sql;
			 	return " select * from  " + " ( "+sql+" ) "+" f "+" LIMIT "+p_iStartRow+" , "+(p_iEndRow-p_iStartRow)+" ";
			//return sql+" LIMIT "+p_iStartRow+" , "+(p_iEndRow-p_iStartRow)+" ";
		case 6:
			return null;
		}
		return null;
	}

	public PreparedStatement getPreparedStatement(String sql)
			throws Exception {
		try {
			log(sql);
			return this.connection.prepareStatement(sql);
		} catch (Exception ex) {
			
			throw ex;
		}
	}

	public void log(String str) {	
		println("LOG: [" + super.getClass().getName() + "] " + str);
	}
	
	
	
	/**
	 **************************************************************************
	 * 
	 * Connection  常用属性和常用操作  end
	 * 
	 **************************************************************************
	 */
	
	
	
	public int execute(String sql,Vector params) throws Exception {
		PreparedStatement pst=this.connection.prepareStatement(sql);
		int resut=-1;
		try {
			this.setSQLValues(pst, params);
		} catch (Exception e) {
		  throw e;	
		}
		resut=pst.executeUpdate();
		pst.close();
		//设置参数
		return resut;
	}

	public int execute(String sql) throws Exception {
		//设置参数
		return execute(sql,null);
	}
	
	
	
	/**
	 **************************************************************************
	 * 
	 * 执行SQL语句操作 例如 delete update add等操作  begin
	 * 
	 **************************************************************************
	 */
	
	
	
	/**
	 **************************************************************************
	 * 
	 * 执行SQL语句操作 例如 delete update add等操作 end
	 * 
	 **************************************************************************
	 */
	
	
	/**
	 **************************************************************************
	 * 
	 * 执行查询SQL语句操作 begin
	 * 
	 **************************************************************************
	 */


	
	/**
	 * 根据传入sql语句,传入参数返回HashMap格式的list结果集 sql语句必须为 as格式如 select id as id, name as
	 * name from demo
	 * 
	 * @param sql
	 *            传过来的sql语句
	 * @param params
	 *            传入参数
	 * @return List HashMap格式list结果集
	 */
	public List query(String sql,Vector params, int startRow, int endRow) throws Exception {
		
			DBReader dbr = new DBReader();
	    	String rownum_sql=sql;
			PreparedStatement pst;
			
			if(startRow<endRow){//设置分页SQL语句
				rownum_sql=setRownumControl(sql,startRow,endRow);
			}
			pst=this.connection.prepareStatement(rownum_sql);
			if (params != null) {
				setSQLValues(pst, params);
			}
			ResultSet resultSet=pst.executeQuery();//执行操作	
			dbr.executeQuery(resultSet); //封装值
			resultSet.close();
			pst.close();
				
		return dbr.getResultList();
	}
	
	
	/**
	 **************************************************************************
	 * 
	 * 执行查询SQL语句操作 end
	 * 
	 **************************************************************************
	 */
	
	
	
	/**
	 **************************************************************************
	 * 
	 * 执行添加SQL语句操作 begin
	 * 
	 **************************************************************************
	 */
	
	
	
	public int insert(String sql)
			throws Exception {
		return execute(sql,null);
	}
	public int insert(String sql,
			Vector params) throws Exception {
		return execute(sql, params) ;
	}
	public int insert(String tablename, List cols, Vector params)
			throws Exception {
		StringBuffer insertSql = new StringBuffer();
		insertSql.append("insert into ");
		insertSql.append(tablename);
		insertSql.append(" (");
		for (int i = 0; i < cols.size(); ++i) {
			insertSql.append((String) cols.get(i));
			if (i == cols.size() - 1) {
				insertSql.append(")");
			} else {
				insertSql.append(",");
			}
		}
		insertSql.append(" values(");
		for (int i = 0; i < cols.size(); ++i) {
			if (i == cols.size() - 1) {
				insertSql.append("?)");
			} else {
				insertSql.append("?,");
			}
		}
		return execute(insertSql.toString(), params);
	}
	public int update(String sql)
			throws Exception {
		return execute(sql);
	}
	
	
	/**
	 **************************************************************************
	 * 
	 * 执行添加SQL语句操作 end
	 * 
	 **************************************************************************
	 */
	
	
	/**
	 **************************************************************************
	 * 
	 * 执行更新SQL语句操作 begin
	 * 
	 **************************************************************************
	 */
	
	public int update(String tablename, List cols, Vector params,
			String condtions) throws Exception {
		StringBuffer updateSql = new StringBuffer();
		updateSql.append("update ");
		updateSql.append(tablename);
		updateSql.append(" set  ");
		for (int i = 0; i < cols.size(); ++i) {
			updateSql.append((String) cols.get(i));
			updateSql.append("=?");
			if (i == cols.size() - 1)
				continue;
			updateSql.append(", ");
		}
		updateSql.append(condtions);
		return execute(updateSql.toString(), params);
	}
	public int update(String sql,
			Vector params) throws Exception {
		return execute(sql, params);
	}
	
	
	/**
	 **************************************************************************
	 * 
	 * 执行更新SQL语句操作 end
	 * 
	 **************************************************************************
	 */
	
	/**
	 **************************************************************************
	 * 
	 * 执行删除SQL语句操作 begin
	 * 
	 **************************************************************************
	 */
	public int delete(String sql)
			throws Exception {
		return execute(sql);
	}
	
	public int delete(String sql,Vector params)
	       throws Exception {
         return execute(sql,params);
     }

	/**
	 **************************************************************************
	 * 
	 * 执行删除SQL语句操作 end
	 * 
	 **************************************************************************
	 */
	
	
	
	
	

	/***************************************************************************************************
	 * 
	 * 1)不分页结果集返回操作 2)分页结果集返回操作
	 * 
	 * *************************************************************************

	 */

	/**
	 * 根据传入sql语句返回HashMap格式的list结果集 sql语句必须为 as格式如 select id as id, name as name
	 * from demo
	 * 
	 * @param sql
	 *            传过来的sql语句
	 * @return List HashMap格式list结果集
	 */

	public List list(String sql) throws Exception {
		return  query(sql, null, -1, -1);
	}

	/**
	 * 根据传入sql语句,传入参数返回HashMap格式的list结果集 sql语句必须为 as格式如 select id as id, name as
	 * name from demo
	 * 
	 * @param sql
	 *            传过来的sql语句
	 * @param params
	 *            传入参数
	 * @return List HashMap格式list结果集
	 */
	public List list(String sql, Vector params) throws Exception {
		
		return query(sql, params, -1, -1);
	}

	/**
	 * 根据传入sql语句，记录开始位置，记录结束位置返回HashMap格式的list结果集 sql语句必须为 as格式如 select id as
	 * id, name as name from demo
	 * 
	 * @param sql
	 *            传过来的sql语句
	 * @param p_iStartRow
	 *            记录开始位置
	 * @param p_iEndRow
	 *            记录结束位置
	 * @return List list结果集
	 */
	public List list(String sql, int startRow,
			int endRow) throws Exception {
		
		return query(sql, null, startRow,endRow);
	}

	/**
	 * 根据传入sql语句，传入参数，记录开始位置，记录结束位置返回HashMap格式的list结果集 sql语句必须为 as格式如 select id
	 * as id, name as name from demo
	 * 
	 * @param sql
	 *            传过来的sql语句
	 * @param params
	 *            传入参数
	 * @param p_iStartRow
	 *            记录开始位置
	 * @param p_iEndRow
	 *            记录结束位置
	 * @return List list结果集
	 */
	public List list(String sql, Vector params,
			int startRow, int endRow) throws Exception {
		return query(sql, params,startRow, endRow);
	}

	
}
